package com.apes.fn.rpt.profit.grossProfitProcess.processor;

import com.apes.fn.rpt.profit.grossProfitProcess.CommonGrossProfitProcess;
import com.apes.fn.rpt.profit.grossProfitProcess.GrossProfitProcess;
import org.springframework.stereotype.Component;

import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * 零售单
 */
@Component
public class RetailExpense extends CommonGrossProfitProcess implements GrossProfitProcess {

    private final static List<String> BODY_RESULT_LIST = Arrays.asList(
            "CREATE_DATE","DOCUMENT_DATE","SERVE_TYPE_NAME","SERVE_TYPE_ID","DEPT_ID","COMPANY_ID","PRICE_TOTAL",
            "COST","PLATFORM_MANAGEMENT_FEE","SUPPLY_CHAIN_MANAGEMENT_FEE","CONSUMABLE_USE","ACCESSORY_COST",
            "MAN_HOUR_COST","VIP_IN","VIP_OUT"
    );
    private final static List<String> BASE_GROUP_BY_LIST = Arrays.asList(
            "CREATE_DATE","DOCUMENT_DATE","SERVE_TYPE_NAME","SERVE_TYPE_ID","DEPT_ID","COMPANY_ID"
    );

    private static Integer TYPE = 1;

    /**
     * 正常毛利汇总SQL
     */
    public final static Integer DEFAULT_TYPE = 1;
    /**
     * 待记账的sql
     */
    public final static Integer DOCUMENT_DATE_TYPE = 2;

    @Override
    public String getSQL(Date taskDate){

        String SQL = "SELECT X.CREATE_DATE, X.DOCUMENT_DATE, " +
                "             '服务'                                                                      INCOME_CATEGORIES, " +
                "             X.SERVE_TYPE_NAME, " +
                "             X.DEPT_ID, " +
                "             X.COMPANY_ID, " +
                "             nvl((" + getAmount() + "),0)                                             AMOUNT, " +
                "             nvl((" + getTimes() + "),0)                                             TIMES, " +
                "             SUM(X.PRICE_TOTAL)                                                        PRICE_TOTAL, " +
                "             SUM(X.COST)                                                               COST, " +
                "             SUM(X.CONSUMABLE_USE) " +
                "                                                                                       CONSUMABLE_USE, " +
                "             SUM(X.ACCESSORY_COST)                                                     ACCESSORY_COST, " +
                "             SUM(X.MAN_HOUR_COST)                                                      MAN_HOUR_COST, " +
                "             SUM(X.PLATFORM_MANAGEMENT_FEE) " +
                "                                                                                       PLATFORM_MANAGEMENT_FEE, " +
                "             SUM(X.SUPPLY_CHAIN_MANAGEMENT_FEE)                                        SUPPLY_CHAIN_MANAGEMENT_FEE," +
                "             SUM(VIP_IN)                                                               VIP_IN, " +
                "             SUM(VIP_OUT)                                                              VIP_OUT" +
                "      FROM (" + getBody() + ") X " +
                "      GROUP BY X.CREATE_DATE,X.DOCUMENT_DATE, X.SERVE_TYPE_ID, X.SERVE_TYPE_NAME, X.DEPT_ID, " +
                "               X.COMPANY_ID";
        TYPE = DEFAULT_TYPE;
        return SQL;
    }

    @Override
    public void changeType(Integer typeNumber){
        switch (typeNumber){
            case 1:
                TYPE = DEFAULT_TYPE;
                break;
            case 2:
                TYPE = DOCUMENT_DATE_TYPE;
                break;
                default:throw new RuntimeException("不支持的操作");
        }
    }

    /**
     * 台次
     * @return
     */
    private String getTimes() {
        return "SELECT SUM(TIMES) " +
                "FROM (select DEPT_ID,DATE_OF_STATISTICS,SERVE_TYPE_ID,(case when sum(TIMES) > 1 then 1 when sum(TIMES) < -1 then -1 else sum(TIMES) end) TIMES\n" +
                "       from RCL_SALES_TIMES_BUSINESS S\n" +
                "       WHERE S.DATE_OF_STATISTICS >= to_date(:P_DATE, 'YYYY-mm-dd')\n" +
                "       AND S.DATE_OF_STATISTICS < to_date(:P_DATE, 'YYYY-mm-dd') +1\n" +
                "       group by DEPT_ID,DATE_OF_STATISTICS,SERVE_TYPE_ID,CAR_LICENSE" +
                ")A  " +
                "   WHERE  A.DEPT_ID = X.DEPT_ID" +
                "   and   A.SERVE_TYPE_ID = X.SERVE_TYPE_ID" +
                "       ";
    }

    /**
     * 金额
     * @return
     */
    private String getAmount() {
        return "SELECT SUM(AMOUNT) " +
                "              FROM (SELECT B.PRICE_TOTAL - B.DISCOUNT_AMOUNT AMOUNT, " +
                "                           A.DEPT_ID, " +
                "                           P.CLASSIFY_ID " +
                "                    FROM RETAIL_ORDER A " +
                "                             LEFT JOIN RETAIL_ORDER_ITEM B ON A.ID = B.SALE_ID " +
                "                             LEFT JOIN " +
                "                         MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "                    WHERE A.CLAIM_TYPE IS NULL " +
                "                      AND A.DATE_APPROVE >= to_date(:P_DATE, 'YYYY-mm-dd') " +
                "                      AND A.DATE_APPROVE < to_date(:P_DATE, 'YYYY-mm-dd') + 1 " +
                "                    UNION ALL " +
                "                    SELECT B.PRICE_TOTAL " +
                "                               - B.DISCOUNT AMOUNT, " +
                "                           A.DEPT_ID, " +
                "                           P.CLASSIFY_ID " +
                "                    FROM ACC_VOUCHER A " +
                "                             LEFT JOIN ACC_VOUCHER_ITEM B ON A.ID = B.VOUCHER_ID " +
                "                             LEFT JOIN MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "                    WHERE A.VOUCHER_TYPE " +
                "                        = 'CUSTOMER_SALE' " +
                "                      AND A.INVENTORY_MODE = 'IM_OWN' " +
                "                      AND ((SELECT t.Party_Id " +
                "                            FROM MAS_PARTY_ROLE t " +
                "                            WHERE T.ID = A.COMPANY_ID) != " +
                "                           (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = A.PARTNER_ID)) " +
                "                      and ((a.origin_business_type <> 'RetailReturn' AND (a.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' " +
                "                        OR A.CLASSIFICATION IS NULL)) or (a.origin_business_type = 'RetailReturn' and a.classification " +
                "                        in ('08', '09'))) " +
                "                      and nvl(a.CLASSIFICATION, ' ') <> 'CROSS_IC' " +
                "                      and A.CREATE_DATE >= to_date(:P_DATE, 'YYYY-mm-dd') " +
                "                      AND A.CREATE_DATE < to_date(:P_DATE, 'YYYY-mm-dd') + 1 " +
                "                    UNION ALL " +
                "                    SELECT B.PRICE_TOTAL - " +
                "                           B.DISCOUNT AMOUNT, " +
                "                           A.DEPT_ID, " +
                "                           P.CLASSIFY_ID " +
                "                    FROM ACC_VOUCHER A " +
                "                             LEFT JOIN ACC_VOUCHER_ITEM B " +
                "                                       ON A.ID = B.VOUCHER_ID " +
                "                             LEFT JOIN MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "                    WHERE A.VOUCHER_TYPE " +
                "                        = 'CUSTOMER_SALE' " +
                "                      AND A.INVENTORY_MODE = 'IM_OWN' " +
                "                      AND A.SCENE_ID = 'HC000' " +
                "                      AND A.CREATE_DATE " +
                "                        >= to_date(:P_DATE, 'YYYY-mm-dd') " +
                "                      AND A.CREATE_DATE < to_date(:P_DATE, 'YYYY-mm-dd') " +
                "                        + 1) A " +
                "              WHERE (SELECT SC.SERVE_TYPE_ID " +
                "                     FROM MAS_SERVE_CATEGORY SC " +
                "                     WHERE ROWNUM = 1 " +
                "                       AND SC.CLASSIFY_ID " +
                "                         = A.CLASSIFY_ID) = x.SERVE_TYPE_ID " +
                "                AND A.DEPT_ID = X.DEPT_ID";
    }

    /**
     * 主体
     * @return
     */
    private String getBody() {
        StringBuffer sf = new StringBuffer();
        //管理费
        sf.append(getManagementFee());
        sf.append("      UNION ALL ");
        //耗材成本
        sf.append(getConsumableUse());
        sf.append("      UNION ALL ");
        //配件成本
        sf.append(getAccessoryCost());
        sf.append("      UNION ALL ");
        //工时成本
        sf.append(getManHourCost());
        if (TYPE.equals(DOCUMENT_DATE_TYPE))return sf.toString();
        sf.append("      UNION ALL ");
        //会员联盟收入
        sf.append(getVipIn());
        sf.append("      UNION ALL ");
        //会员联盟支出
        sf.append(getVipOut());

        return sf.toString();
    }

    /**
     * 查询管理费
     * @return:
     * PRICE_TOTAL:记账金额
     * COST:成本
     * PLATFORM_MANAGEMENT_FEE:平台管理费
     * SUPPLY_CHAIN_MANAGEMENT_FEE:供应链管理费
     */
    private String getManagementFee(){

        String core = "SELECT C.CREATE_DATE, " +
                "             C.DOCUMENT_DATE, " +
                "             C.SERVE_TYPE_NAME, " +
                "             C.SERVE_TYPE_ID, " +
                "             C.DEPT_ID, " +
                "             C.COMPANY_ID, " +
                "             C.PRICE_TOTAL, " +
                "             C.COST, " +
                "             C.PLATFORM_MANAGEMENT_FEE, " +
                "             C.SUPPLY_CHAIN_MANAGEMENT_FEE " +
                "      FROM (SELECT TRUNC(A.CREATE_DATE) " +
                "                                                    CREATE_DATE, " +
                "                   TRUNC(V.BOOK_DATE)               DOCUMENT_DATE, " +
                "                   A.SOURCE, " +
                "                   A.ID, " +
                "                   A.ORIGIN, " +
                "                   A.COMPANY_ID, " +
                "                   B.PLATFORM_MANAGEMENT_FEE, " +
                "                   B.SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "                   A.DEPT_ID, " +
                "                   V.SERVE_TYPE_NAME                SERVE_TYPE_NAME, " +
                "                   V.SERVE_TYPE_ID " +
                "                                                    SERVE_TYPE_ID, " +
                "                   (B.PRICE_TOTAL - B.DISCOUNT)     PRICE_TOTAL, " +
                "                   (SELECT SUM(E.TAX_COST_PRICE * E.PRODUCT_QTY) " +
                "                    FROM ACC_VOUCHER_INVENTORY E " +
                "                    WHERE E.VOUCHER_ITEM_ID = B.ID) COST " +
                "            FROM ACC_VOUCHER A " +
                "                     LEFT JOIN " +
                "                 ACC_VOUCHER_ITEM B ON A.ID = B.VOUCHER_ID " +
                "                     LEFT JOIN ACC_VOUCHER_EXTENSION V ON b.ID = v.ID " +
                "            WHERE A.VOUCHER_TYPE = 'CUSTOMER_SALE' " +
                "              AND A.INVENTORY_MODE = 'IM_OWN' " +
                "              AND (A.BUSINESS_TYPE " +
                "                       IN ('RetailOrder', 'SaleOrder', 'SaleReturn', 'RetailReturn') OR " +
                "                   A.ORIGIN_BUSINESS_TYPE IN " +
                "                   ('RetailOrder', 'SaleOrder', 'SaleReturn', 'RetailReturn')) " +
                "              AND A.ORIGIN_BUSINESS_TYPE not in ('ConsumableUse','ProfitAndLoss','InventoryTransferAsset') " +
                "              and A.SOURCE_BUSINESS_TYPE <> 'LossSaleReturn' " +
                "              AND A.CREATE_DATE >= to_date(:P_DATE, 'YYYY-mm-dd') " +
                "              AND A.CREATE_DATE < to_date(:P_DATE, 'YYYY-mm-dd') + 1) C " +
                "      WHERE C.SERVE_TYPE_ID NOT IN " +
                "            ('010', '012') "
                ;


        List<String> list = Arrays.asList("PRICE_TOTAL", "COST", "PLATFORM_MANAGEMENT_FEE", "SUPPLY_CHAIN_MANAGEMENT_FEE");

        return unionPake(core+getAppend(),list,BASE_GROUP_BY_LIST);
    }

    private String getAppend() {
        String append = "";
        if (TYPE.equals(DEFAULT_TYPE)) append ="        and CREATE_DATE = DOCUMENT_DATE";
        if (TYPE.equals(DOCUMENT_DATE_TYPE)) append ="        and CREATE_DATE <> DOCUMENT_DATE";
        return append;
    }

    /**
     * 耗材成本
     * @return
     */
    private String getConsumableUse(){
        String core = "SELECT C.CREATE_DATE, " +
                "             C.DOCUMENT_DATE, " +
                "             C.SERVE_TYPE_NAME, " +
                "             C.SERVE_TYPE_ID, " +
                "             C.DEPT_ID, " +
                "             C.COMPANY_ID, " +
                "             C.PRICE_TOTAL, " +
                "             C.COST, " +
                "             C.COST + c.PLATFORM_MANAGEMENT_FEE + c.SUPPLY_CHAIN_MANAGEMENT_FEE CONSUMABLE_USE, " +
                "             C.PLATFORM_MANAGEMENT_FEE, " +
                "             C.SUPPLY_CHAIN_MANAGEMENT_FEE " +
                "      FROM (SELECT TRUNC(A.CREATE_DATE)             CREATE_DATE, " +
                "                   TRUNC(V.BOOK_DATE) " +
                "                                                    DOCUMENT_DATE, " +
                "                   A.SOURCE, " +
                "                   A.ID, " +
                "                   A.ORIGIN, " +
                "                   A.COMPANY_ID, " +
                "                   B.PLATFORM_MANAGEMENT_FEE, " +
                "                   B.SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "                   A.DEPT_ID, " +
                "                   V.SERVE_TYPE_NAME                SERVE_TYPE_NAME, " +
                "                   V.SERVE_TYPE_ID                  SERVE_TYPE_ID, " +
                "                   (B.PRICE_TOTAL " +
                "                       - B.DISCOUNT)                PRICE_TOTAL, " +
                "                   (SELECT SUM(E.TAX_COST_PRICE * E.PRODUCT_QTY) " +
                "                    FROM ACC_VOUCHER_INVENTORY E " +
                "                    WHERE E.VOUCHER_ITEM_ID = B.ID) COST " +
                "            FROM ACC_VOUCHER A " +
                "                     LEFT JOIN ACC_VOUCHER_ITEM B ON A.ID " +
                "                = B.VOUCHER_ID " +
                "                     LEFT JOIN ACC_VOUCHER_EXTENSION V ON B.ID = V.ID " +
                "            WHERE A.VOUCHER_TYPE = 'CUSTOMER_SALE' " +
                "              AND A.INVENTORY_MODE = 'IM_OWN' " +
                "              AND A.ORIGIN_BUSINESS_TYPE = 'ConsumableUse' " +
                "              AND A.SCENE_ID = 'HC000' " +
                "              AND A.CREATE_DATE " +
                "                >= to_date(:P_DATE, 'YYYY-mm-dd') " +
                "              AND A.CREATE_DATE < to_date(:P_DATE, 'YYYY-mm-dd') " +
                "                + 1) C  where 1=1" ;
        List<String> list = Arrays.asList("CONSUMABLE_USE");
        return unionPake(core+getAppend(),list,BASE_GROUP_BY_LIST);
    }

    /**
     * 配件成本
     * @return
     */
    private String getAccessoryCost(){
        String core = "SELECT C.CREATE_DATE, " +
                "             C.DOCUMENT_DATE, " +
                "             C.SERVE_TYPE_NAME, " +
                "             C.SERVE_TYPE_ID, " +
                "             C.DEPT_ID, " +
                "             C.COMPANY_ID, " +
                "             C.PRICE_TOTAL, " +
                "             C.COST ACCESSORY_COST, " +
                "             C.PLATFORM_MANAGEMENT_FEE, " +
                "             C.SUPPLY_CHAIN_MANAGEMENT_FEE " +
                "      FROM (SELECT TRUNC(A.CREATE_DATE)             CREATE_DATE, " +
                "                   TRUNC(V.BOOK_DATE) " +
                "                                                    DOCUMENT_DATE, " +
                "                   A.SOURCE, " +
                "                   A.ID, " +
                "                   A.ORIGIN, " +
                "                   A.COMPANY_ID, " +
                "                   B.PLATFORM_MANAGEMENT_FEE, " +
                "                   B.SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "                   A.DEPT_ID, " +
                "                   V.SERVE_TYPE_NAME                SERVE_TYPE_NAME, " +
                "                   V.SERVE_TYPE_ID                  SERVE_TYPE_ID, " +
                "                   (B.PRICE_TOTAL " +
                "                       - B.DISCOUNT)                PRICE_TOTAL, " +
                "                   (SELECT SUM(E.TAX_COST_PRICE * E.PRODUCT_QTY) " +
                "                    FROM ACC_VOUCHER_INVENTORY E " +
                "                    WHERE E.VOUCHER_ITEM_ID = B.ID) COST " +
                "            FROM ACC_VOUCHER A " +
                "                     LEFT JOIN ACC_VOUCHER_ITEM B ON A.ID " +
                "                = B.VOUCHER_ID " +
                "                     LEFT JOIN MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "                     LEFT JOIN ACC_VOUCHER_EXTENSION V ON B.ID = V.ID " +
                "            WHERE A.VOUCHER_TYPE = 'CUSTOMER_SALE' " +
                "              AND A.INVENTORY_MODE = 'IM_OWN' " +
                "              AND (A.BUSINESS_TYPE " +
                "                       IN ('RetailOrder', 'SaleOrder', 'SaleReturn', 'RetailReturn') OR " +
                "                   A.ORIGIN_BUSINESS_TYPE IN " +
                "                   ('RetailOrder', 'SaleOrder', 'SaleReturn', 'RetailReturn')) " +
                "              AND A.ORIGIN_BUSINESS_TYPE not in ('ConsumableUse','ProfitAndLoss','InventoryTransferAsset') " +
                "              AND P.PRODUCT_GROUP_ID = '10' " +
                "              AND A.CREATE_DATE >= to_date(:P_DATE, 'YYYY-mm-dd') " +
                "              AND A.CREATE_DATE " +
                "                < to_date(:P_DATE, 'YYYY-mm-dd') + 1) C " +
                "      where 1=1 ";
        List<String> list = Arrays.asList("ACCESSORY_COST");
        return unionPake(core+getAppend(),list,BASE_GROUP_BY_LIST);
    }

    /**
     * 工时成本
     * @return
     */
    private String getManHourCost(){
        String core = "SELECT C.CREATE_DATE, " +
                "             C.DOCUMENT_DATE, " +
                "             C.SERVE_TYPE_NAME, " +
                "             C.SERVE_TYPE_ID, " +
                "             C.DEPT_ID, " +
                "             C.COMPANY_ID, " +
                "             C.PRICE_TOTAL, " +
                "             C.COST MAN_HOUR_COST, " +
                "             C.PLATFORM_MANAGEMENT_FEE, " +
                "             C.SUPPLY_CHAIN_MANAGEMENT_FEE " +
                "      FROM (SELECT TRUNC(A.CREATE_DATE)             CREATE_DATE, " +
                "                   TRUNC(V.BOOK_DATE)               DOCUMENT_DATE, " +
                "                   A.SOURCE, " +
                "                   A.ID, " +
                "                   A.ORIGIN, " +
                "                   A.COMPANY_ID, " +
                "                   B.PLATFORM_MANAGEMENT_FEE, " +
                "                   B.SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "                   A.DEPT_ID, " +
                "                   V.SERVE_TYPE_NAME                SERVE_TYPE_NAME, " +
                "                   V.SERVE_TYPE_ID                  SERVE_TYPE_ID, " +
                "                   (B.PRICE_TOTAL - B.DISCOUNT) " +
                "                                                    PRICE_TOTAL, " +
                "                   (SELECT SUM(E.TAX_COST_PRICE * E.PRODUCT_QTY) " +
                "                    FROM ACC_VOUCHER_INVENTORY E " +
                "                    WHERE E.VOUCHER_ITEM_ID = B.ID) COST " +
                "            FROM ACC_VOUCHER A " +
                "                     LEFT JOIN ACC_VOUCHER_ITEM B ON A.ID = B.VOUCHER_ID " +
                "                     LEFT JOIN MAS_PRODUCT P ON B.PRODUCT_ID = P.ID " +
                "                     LEFT JOIN ACC_VOUCHER_EXTENSION V ON B.ID = V.ID " +
                "            WHERE A.VOUCHER_TYPE = 'CUSTOMER_SALE' " +
                "              AND A.INVENTORY_MODE = 'IM_OWN' " +
                "              AND (A.BUSINESS_TYPE " +
                "                       IN ('RetailOrder', 'SaleOrder', 'SaleReturn', 'RetailReturn') OR " +
                "                   A.ORIGIN_BUSINESS_TYPE IN " +
                "                   ('RetailOrder', 'SaleOrder', 'SaleReturn', 'RetailReturn')) " +
                "              AND A.ORIGIN_BUSINESS_TYPE not in ('ConsumableUse','ProfitAndLoss','InventoryTransferAsset') " +
                "              AND P.PRODUCT_GROUP_ID <> '10' " +
                "              AND A.CREATE_DATE >= to_date(:P_DATE, 'YYYY-mm-dd') " +
                "              AND A.CREATE_DATE < to_date(:P_DATE, 'YYYY-mm-dd') + 1) C " +
                "      where  C.SERVE_TYPE_ID NOT IN ('010', '012')";
        List<String> list = Arrays.asList("MAN_HOUR_COST");
        return unionPake(core+getAppend(),list,BASE_GROUP_BY_LIST);
    }

    /**
     * 会员联盟收入
     * @return
     */
    private String getVipIn(){
        String core = "SELECT TRUNC(A.STATISTIC_DATE) CREATE_DATE,TRUNC(A.STATISTIC_DATE) DOCUMENT_DATE, " +
                "        E.NAME                                                              SERVE_TYPE_NAME, " +
                "        E.ID                                                                SERVE_TYPE_ID, " +
                "        A.DEPT_ID, " +
                "        A.COMPANY_ID, " +
                "        SUM(CASE WHEN VIP_BALANCE_TYPE = '3' THEN B.SETTLEMENT_AMOUNT ELSE 0 END) + " +
                "        SUM(CASE WHEN VIP_BALANCE_TYPE = '1' THEN B.SETTLEMENT_AMOUNT ELSE 0 END) + " +
                "        SUM(CASE WHEN VIP_BALANCE_TYPE = '2' THEN B.SETTLEMENT_AMOUNT ELSE 0 END) - " +
                "        SUM(CASE WHEN VIP_BALANCE_TYPE = '2' THEN B.AMOUNT_USED ELSE 0 END) VIP_IN " +
                " FROM VIP_UNION_SETTLE A, " +
                "      VIP_UNION_SETTLE_ITEM B " +
                "          LEFT JOIN MAS_PRODUCT C ON B.PRODUCT_ID = C.ID " +
                "          LEFT JOIN MAS_SERVE_CATEGORY D ON D.CLASSIFY_ID = C.CLASSIFY_ID AND ROWNUM = 1 " +
                "          LEFT JOIN MAS_SERVE_TYPE E ON E.ID = D.SERVE_TYPE_ID " +
                " WHERE A.ID = B.VIP_UNION_SETTLE_ID " +
                "   AND A.TYPE = 'in' " +
                "   AND A.STATISTIC_DATE >= TO_DATE(:P_DATE, 'YYYY-mm-dd') " +
                "   AND A.STATISTIC_DATE < TO_DATE(:P_DATE, 'YYYY-mm-dd') + 1 " +
                " GROUP BY TRUNC(A.STATISTIC_DATE), E.NAME, E.ID, A.DEPT_ID, A.COMPANY_ID";
        List<String> list = Arrays.asList("VIP_IN");
        return unionPake(core,list,BASE_GROUP_BY_LIST);
    }

    /**
     * 会员联盟支出
     * @return
     */
    private String getVipOut(){
        String core = "SELECT TRUNC(A.STATISTIC_DATE) CREATE_DATE,TRUNC(A.STATISTIC_DATE) DOCUMENT_DATE, " +
                "        E.NAME                                                              SERVE_TYPE_NAME, " +
                "        E.ID                                                                SERVE_TYPE_ID, " +
                "        A.DEPT_ID, " +
                "        A.COMPANY_ID, " +
                "        SUM(CASE WHEN VIP_BALANCE_TYPE = '3' THEN B.SETTLEMENT_AMOUNT ELSE 0 END) + " +
                "        SUM(CASE WHEN VIP_BALANCE_TYPE = '1' THEN B.SETTLEMENT_AMOUNT ELSE 0 END) + " +
                "        SUM(CASE WHEN VIP_BALANCE_TYPE = '2' THEN B.SETTLEMENT_AMOUNT ELSE 0 END) + " +
                "        SUM(CASE WHEN VIP_BALANCE_TYPE = '2' THEN B.AMOUNT_USED ELSE 0 END) VIP_OUT " +
                " FROM VIP_UNION_SETTLE A, " +
                "      VIP_UNION_SETTLE_ITEM B " +
                "          LEFT JOIN MAS_PRODUCT C ON B.PRODUCT_ID = C.ID " +
                "          LEFT JOIN MAS_SERVE_CATEGORY D ON D.CLASSIFY_ID = C.CLASSIFY_ID AND ROWNUM = 1 " +
                "          LEFT JOIN MAS_SERVE_TYPE E ON E.ID = D.SERVE_TYPE_ID " +
                " WHERE A.ID = B.VIP_UNION_SETTLE_ID " +
                "   AND A.TYPE = 'out' " +
                "   AND A.STATISTIC_DATE >= TO_DATE(:P_DATE, 'yyyy-mm-dd') " +
                "   AND A.STATISTIC_DATE < TO_DATE(:P_DATE, 'yyyy-mm-dd') + 1 " +
                " GROUP BY TRUNC(A.STATISTIC_DATE), E.NAME, E.ID, A.DEPT_ID, A.COMPANY_ID";
        List<String> list = Arrays.asList("VIP_OUT");
        return unionPake(core,list,BASE_GROUP_BY_LIST);
    }

    /**
     * 拼接 UNION ALL 所需对应列返回值
     * @param core 核心sql
     * @param returnList 当前核心sql返回对应列的值
     * @param groupByList 分组字段
     * @return
     */
    private String unionPake(String core, List<String> returnList, List<String> groupByList){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT ");
        //计算需要返回的sql
        List<String> bodyResultList = BODY_RESULT_LIST;
        for (int i = 0; i < bodyResultList.size(); i++) {
            String bodyResult = bodyResultList.get(i);
            //拼接分组的字段
            if (groupByList.contains(bodyResult)){
                sf.append(bodyResult+" "+bodyResult);
                if (i!=bodyResultList.size()-1)sf.append(",");
                continue;
            }
            //拼接外层需要的字段
            if (returnList.contains(bodyResult)){
                sf.append("SUM("+bodyResult+") "+bodyResult);
                if (i!=bodyResultList.size()-1)sf.append(",");
                continue;
            }
            //不需要的字段
            sf.append("0 "+bodyResult);
            if (i!=bodyResultList.size()-1)sf.append(",");
        }

        //拼接核心sql
        sf.append(" FROM ("+core+" ) D ");

        //拼接分组
        sf.append(" GROUP BY ");
        for (int i = 0; i < groupByList.size(); i++) {
            sf.append(groupByList.get(i));
            if (i!=groupByList.size()-1)sf.append(",");
        }
        return sf.toString();
    }



    @Override
    public boolean support(String type) {
        return "Retail".equals(type);
    }
}
